package com.fengpt.mysqlback.util;

import java.sql.*;

public class SqlUtils {

    /**
     * oracle生成插入语句语句
     *
     * @param tableName
     * @param whereSql
     * @param stmt
     * @return
     */
    public static String genOracleInsertInto(String tableName, String whereSql, Statement stmt) {
        ResultSet results = null;
        if (tableName.equals(null)) {
            return "/* No table! */\n";
        }
        // 存放表属性名
        String[] columnNames;
        // 存放一条记录的值
        String[] oneRowData;
        String insertOneTalbeDatas = null;
        // 查询结果属性
        ResultSetMetaData rsma = null;
        // 列数
        int numCols = 0;
        // 行数
        int numRows = 0;
        // 查询语句内容
        String queryStr = "SELECT * FROM " + tableName;
        if (whereSql != null && whereSql.length() > 0) {
            queryStr = queryStr + " where " + whereSql;
        }

        try {
            // 获取一个查询结果集为一张表的内容
            results = stmt.executeQuery(queryStr);
            // 表的记录数
            numRows = 0;
            boolean more = results.next();
            while (more) {
                numRows++;
                more = results.next();
            }
            // 初始化获取查询记录集属性
            rsma = results.getMetaData();
            // 初始化表的列数
            numCols = rsma.getColumnCount();
            columnNames = new String[numCols];
            // 初始化获取列名
            for (int i = 0; i < numCols; i++) {
                columnNames[i] = new String(rsma.getColumnName(i + 1));
            }
            // 初始化存放一条记录的数据
            oneRowData = new String[numCols];
        } catch (Exception e) {
            String warning = "/* Not found the table: " + tableName + "! */\n";
            System.out.println("three" + e.getMessage());
            return warning;
        }
        try {
            // 获取一个查询结果集为一张表的内容
            results = stmt.executeQuery(queryStr);
            // 初始化获取查询记录集属性
            rsma = results.getMetaData();
            //获取记录的内容
            StringBuffer insertAllRows = new StringBuffer();
            boolean more = results.next();
            int rowCounts = 0;
            while (more) {
                for (int i = 1; i <= numCols; i++) {
                    oneRowData[i - 1] = results.getString(i);
                }
                insertAllRows.append("insert into " + tableName.trim() + " (");
                for (int i = 0; i < numCols; i++) {
                    if (i != 0) {
                        insertAllRows.append(',');
                    }
                    insertAllRows.append(columnNames[i]);
                }
                insertAllRows.append(") values (");
                String columnType = "";
                for (int i = 0; i < numCols; i++) {
                    if (i != 0) {
                        insertAllRows.append(',');
                    }
                    columnType = rsma.getColumnTypeName(i + 1);
                    columnType = columnType.toUpperCase();
                    if (columnType.indexOf("VAR") >= 0 ||
                            columnType.indexOf("CHAR") >= 0 ||
                            columnType.indexOf("DATE") >= 0 ||
                            columnType.indexOf("TIME") >= 0 ||
                            columnType.indexOf("TEXT") >= 0 ||
                            columnType.indexOf("BINARY") >= 0) {
                        if (columnType.indexOf("DATE") >= 0) {
                            //  String NotNull="TO_DATE(\'" +   oneRowData[i].trim() + "', 'YYYY-MM-DD HH24:MI:SS')";
                            insertAllRows.append("" +
                                    ((oneRowData[i] == null ||
                                            oneRowData[i].length() == 0) ? "NULL" :
                                            "TO_DATE(\'" + oneRowData[i].trim() + "', 'YYYY-MM-DD HH24:MI:SS')"));
                        } else {
                            insertAllRows.append("'" +
                                    ((oneRowData[i] == null ||
                                            oneRowData[i].length() == 0) ? "" :
                                            oneRowData[i].trim()) + "'");
                        }
                    } else {
                        insertAllRows.append(((oneRowData[i] == null ||
                                oneRowData[i].length() == 0) ? "\'\'" :
                                oneRowData[i]));
                    }
                }
                insertAllRows.append(");\n");
                more = results.next();
            }
            results.close();
            if (insertAllRows.length() != 0) {
                insertOneTalbeDatas = new String(insertAllRows);
            } else {
                insertOneTalbeDatas = "/* Not found data in the table: " + tableName +
                        "! */\n";
            }
            insertAllRows.delete(0, insertAllRows.length() - 1);
        } catch (Exception e) {
            String warning = "/* Not found the table: " + tableName + "! */\n";
            System.out.println("four");
            e.printStackTrace();
            return warning;
        }
        return insertOneTalbeDatas;
    }

    /**
     * 插入sql
     */
    private static String insert = "INSERT INTO";

    /**
     * values关键字
     */
    private static String values = "VALUES";


    /**
     *
     * @param stmt
     * @param tableName
     * @param whereSql
     * @return
     * @throws SQLException
     */
    public static String getMysqlInsertSql(Statement stmt, String tableName, String whereSql)
            throws SQLException {
        ResultSet results = null;
        if (tableName.equals(null)) {
            return "/* No table! */\n";
        }
        // 查询语句内容
        String queryStr = "SELECT * FROM " + tableName;
        if (whereSql != null && whereSql.length() > 0) {
            queryStr = queryStr + " where " + whereSql;
        }
        System.out.println(queryStr);
        StringBuffer insertSQL = new StringBuffer();
        try {
            // 获取一个查询结果集为一张表的内容
            results = stmt.executeQuery(queryStr);
            ResultSetMetaData rsmd = results.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (results.next()) {
                StringBuffer columnName = new StringBuffer();
                StringBuffer columnValue = new StringBuffer();

                for (int i = 1; i <= columnCount; i++) {
                    String value = results.getString(i);
                    if (i == columnCount) {
                        columnName.append(rsmd.getColumnName(i));
                        if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {
                            if (value == null) {
                                columnValue.append("null");
                            } else {
                                columnValue.append("'").append(value).append("'");
                            }
                        } else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)) {
                            if (value == null) {
                                columnValue.append("null");
                            } else {
                                columnValue.append(value);
                            }
                        } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                            if (value == null) {
                                columnValue.append("null");
                            } else {
                                //append("timestamp'")
                                columnValue.append(value).append("'");
                            }
                        } else {
                            if (value == null) {
                                columnValue.append("null");
                            } else {
                                columnValue.append(value);
                            }
                        }
                    } else {
                        columnName.append(rsmd.getColumnName(i) + ",");
                        if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {
                            if (value == null) {
                                columnValue.append("null,");
                            } else {
                                columnValue.append("'").append(value).append("',");
                            }
                        } else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)) {
                            if (value == null) {
                                columnValue.append("null,");
                            } else {
                                columnValue.append(value).append(",");
                            }
                        } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                            if (value == null) {
                                columnValue.append("null,");
                            } else {
                                //append("timestamp'")
                                columnValue.append(value).append("',");
                            }
                        } else {
                            if (value == null) {
                                columnValue.append("null,");
                            } else {
                                columnValue.append(value).append(",");
                            }
                        }
                    }
                }
                insertSQL.append(insert).append(" ").append(tableName).append("(").append(columnName.toString()) .append(")").append(values).append("(").append(columnValue.toString()).append(");\n");
            }
        } catch (Exception e) {
            String warning = "/* Not found the table: " + tableName + "! */\n";
            System.out.println("three" + e.getMessage());
            return warning;
        }

        return insertSQL.toString();
    }

}
